package MySQLConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;

import common.Enums.ComponentType;
import common.Enums.DateRange;
import common.Exceptions.DAOException;
import common.Utils.SimpleLock;

import DAO.BcDAO;
import DAO.CaseDAO;
import DAO.CriterionDAO;
import DAO.DAOFactory;
import DAO.FormulaDAO;
import DAO.MetAppDAO;
import DAO.MethodDAO;
import DAO.SolutionDAO;
import DAO.ValueDAO;

public class MySqlDAOFactory extends DAOFactory 
{
	public static final String DRIVER = "com.mysql.jdbc.Driver";
	public static final String DBURL = "jdbc:mysql://localhost:3306/root_bcframework";
	public static final String DBName = "root_bcframework";
	public static final String RELIABILITY_TYPE = "reliabilitytype";
	public static final String METHOD_TYPE = "methodtype";
	public static final String SOURCE_TYPE = "sourcetype";
	private Connection connection;
	private static MySqlDAOFactory instance;
	public static final SimpleLock lockConnection = new SimpleLock();
	
	public static MySqlDAOFactory getInstance() 
	{
		if(instance == null)
			instance = new MySqlDAOFactory();
		
		return instance;
	}
	
	public MySqlDAOFactory()
	{
	}
	
	@Override
	public SolutionDAO getSolutionDAO() 
	{
		return MySQLSolutionDAO.getInstance();
	}
	
	@Override
	public BcDAO getBcDAO() 
	{
		return MySQLBcDAO.getInstance();
	}
	
	@Override
	public CaseDAO getCaseDAO() 
	{
		return MySQLCaseDAO.getInstance();
	}
	
	@Override
	public ValueDAO getValueDAO() 
	{
		return MySQLValueDAO.getInstance();
	}
	
	@Override
	public MethodDAO getMethodDAO() 
	{
		return MySQLMethodDAO.getInstance();
	}

	@Override
	public CriterionDAO getCriterionDAO() 
	{
		return MySQLCriterionDAO.getInstance();
	}
	
	@Override
	public FormulaDAO getFormulaDAO() 
	{
		return MySQLFormulaDAO.getInstance();
	}
	
	@Override
	public MetAppDAO getMetAppDAO() 
	{
		return MySQLMetAppDAO.getInstance();
	}
	
	public Statement createStatement() throws DAOException, SQLException
	{
		if(connection == null)
			connection = createConnection();
		
		return connection.createStatement();
	}
	
	public PreparedStatement prepareStatement(String sql) throws DAOException, SQLException
	{
		if(connection == null)
			connection = createConnection();
		
		return connection.prepareStatement(sql);
	}
	
	private Connection createConnection() throws DAOException
	{
		String user = "root";
		String password = "d27m10";
		
		try 
		{
			Class.forName(DRIVER);
			connection = DriverManager.getConnection(DBURL, user, password);
		} 
		catch (ClassNotFoundException e) 
		{
			throw new DAOException("The driver " + DRIVER + " wasn't found by the application. Make sure that the driver is in the correct place (if the server running is tomcat the driver must be at Tomcat/lib directory)");
		}
		catch (SQLException e) 
		{
			throw new DAOException("MySQL connection couldn't be opened: " + e.getMessage(), e);
		} 
		
		return connection;
	}
	
	public static String getEnumFromDatabase(String table, String itemID) throws DAOException, SQLException
	{
		String retVal = null;
		
		String query = "SELECT name FROM table WHERE id = 'itemID';";
		query = query.replace("table", table).replace("itemID", itemID);
		Statement statement = MySqlDAOFactory.getInstance().createStatement();
		ResultSet dataSet = statement.executeQuery(query);
		
		if(dataSet.next())
			retVal = dataSet.getString("name");
		
		return retVal;
	}
	
	public static int getEnumIdByName(String table, String itemName) throws DAOException, SQLException
	{
		int retVal = 0;
		
		String query = "SELECT id FROM table WHERE name = 'itemName';";
		query = query.replace("table", table).replace("itemName", itemName);
		Statement statement = MySqlDAOFactory.getInstance().createStatement();
		ResultSet dataSet = statement.executeQuery(query);
		
		if(dataSet.next())
			retVal = dataSet.getInt("id");
		
		return retVal;
	}
	
	public void setAutoCommit(boolean autoCommit)
	{
		try {
			if(connection == null)
				connection = createConnection();
			
			connection.setAutoCommit(autoCommit);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void rollbackChanges()
	{
		try {
			connection.rollback();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void commitChanges()
	{
		try {
			connection.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void closeConnection() throws DAOException
	{
		try 
		{
			if(connection != null && !lockConnection.isLocked())
			{
				connection.close();
				connection = null;
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			throw new DAOException("MySQL connection couldn't be closed: " + e.getMessage(), e);
		}
	}
	
	public static String setUpFilters(ComponentType cmpType, String search, DateRange dtRange)
	{
		String appendQuery = "";
		
		if(search != null && !search.trim().equals(""))
			appendQuery += " WHERE NAME_COLUMN like '%" + search.trim() + "%'";
		
		String createdDate = "";
		switch(cmpType)
		{
			case CASE:
				appendQuery = appendQuery.replace("NAME_COLUMN", "cas_name");
				createdDate = " cas_lastUpdated >= ";
				break;
			case CRITERION:
				appendQuery = appendQuery.replace("NAME_COLUMN", "crt_name");
				createdDate = " crt_lastUpdated >= ";
				break;
			case METHOD:
				appendQuery = appendQuery.replace("NAME_COLUMN", "met_name");
				createdDate = " met_lastUpdated >= ";
				break;
		}
			
		if(dtRange != null)
		{
			Timestamp date = new java.sql.Timestamp(MySqlDAOFactory.getLastUpdatedFilter(dtRange).getTime());
			String conditionClausule = (appendQuery.contains("WHERE")) ? " AND " : " WHERE";
			appendQuery += conditionClausule + createdDate + "'" + date + "'";
		}
		
		return appendQuery;
	}
	
	public static java.util.Date getLastUpdatedFilter(DateRange dateRange)
	{
		java.util.Date lastUpdatedFilter = null;
		
		if(dateRange != null)
		{
			Calendar cal = Calendar.getInstance();
			cal.setTime(new java.util.Date());
			cal.set(Calendar.HOUR_OF_DAY, 0);
			
			switch(dateRange)
			{
				case YESTERDAY:
					cal.set(Calendar.DAY_OF_MONTH, cal.get(Calendar.DAY_OF_MONTH) -1);
					break;
					
				case LAST_WEEK:
					cal.set(Calendar.DAY_OF_MONTH, cal.get(Calendar.DAY_OF_MONTH) -7);
					break;
					
				case LAST_MONTH:
					cal.set(Calendar.MONTH, cal.get(Calendar.MONTH) - 1);
					break;
					
				case LAST_YEAR:
					cal.set(Calendar.YEAR, cal.get(Calendar.YEAR) - 1);
					break;
			}
			
			lastUpdatedFilter = cal.getTime();
		}
		
		return lastUpdatedFilter;
	}
}
